<?php

class Queries
{

    public $conn_;

    public function __construct($dblink)
    {

        $this->conn_ = $dblink;
    }

    public function generate_insert($fields, $table)
    {
        $query = "INSERT INTO $table (";

        $cont = 1;
        foreach ($fields as $key => $value) {
            if ($key != 'insert') {
                if ($cont == count($fields) - 1) {
                    $query .= "$key";
                } else {
                    $query .= "$key,";
                }
            }
            $cont++;
        }


        $query .= ") VALUES (";

        $cont = 1;
        foreach ($fields as $key => $value) {
            if ($key != 'insert') {
                if ($cont == count($fields) - 1) {
                    $query .= "'$value'";
                } else {
                    $query .= "'$value',";
                }
            }
            $cont++;
        }

        $query .= ")";

        $query = mysqli_query($this->conn_, $query);
    }

    public function generate_insert_file($fields, $table, $file, $img_dir)
    {

        $query = "INSERT INTO $table (";

        $cont = 1;
        foreach ($fields as $key => $value) {
            if ($key != 'insert') {
                if ($cont == count($fields) - 1) {
                    $query .= "$key";
                } else {
                    $query .= "$key,";
                }
            }
            $cont++;
        }


        $query .= ") VALUES (";

        $cont = 1;
        foreach ($fields as $key => $value) {
            if ($key != 'insert') {
                if ($cont == count($fields) - 1) {
                    $query .= "'$value'";
                } else {
                    $query .= "'$value',";
                }
            }
            $cont++;
        }

        $query .= ")";

        $query = mysqli_query($this->conn_, $query);

        if ($query AND !empty($file)) {

            $filename = $file["file"]["name"];
            $path = $img_dir . $filename;

            $upload = move_uploaded_file($file["file"]["tmp_name"], $path);

            if ($upload) {
                $id = mysqli_insert_id($this->conn_);
                mysqli_query($this->conn_, "UPDATE $table SET file = '$filename' WHERE id_$table = $id");
            }
        }
    }

    public function get_items($table)
    {

        $query = mysqli_query($this->conn_, "SELECT * from $table");

        if ($query->num_rows > 0) {
            while ($row = $query->fetch_assoc()) {

                $items[] = $row;
            }

            return $items;
        }
    }

    public function get_items_fk($fk)
    {
        $table = key($fk);
        $field = current($fk);

        $query = mysqli_query($this->conn_, "SELECT id_$table,$field FROM $table");

        if ($query->num_rows > 0) {
            $items = "<option></option>";
            while ($row = $query->fetch_array()) {

                $items .= "<option value='" . $row["id_$table"] . "'>" . $row["$field"] . "</option>";
            }

            return $items;
        }
    }

    public function delete_item($id, $table)
    {
        $query = $this->conn_->query("DELETE FROM $table WHERE id_$table = $id LIMIT 1");

    }

    public function delete_item_file($id, $table, $img_dir)
    {

        //getting the file name
        $query = $this->conn_->query("SELECT file FROM $table WHERE id_$table = $id");
        $filename = $query->fetch_assoc();

        $query = $this->conn_->query("DELETE FROM $table WHERE id_$table = $id");

        //delete file from system
        if ($query) {

            unlink($img_dir . $filename["file"]);
        }

    }
}

?>